# -*- coding: utf-8 -*-

"""
PyQt5 tutorial

In this example, we determine the event sender
object.

author: py40.com
last edited: 2017年3月
"""
import sys

import datetime
from PyQt5.QtWidgets import (QMainWindow, QTextEdit,
                             QAction, QFileDialog, QApplication, QHBoxLayout, QVBoxLayout, QLabel, QWidget,
                             QTableWidget, QTableWidgetItem)
from PyQt5.QtGui import QIcon

from core.sqlite3db.SqliteInner import SqliteInner


class DataMangement(QMainWindow):
    DBPathTitle = "数据库路径："

    def __init__(self):
        super().__init__()

        self.initUI()

    def initUI(self):

        self.filePath = None

        # 菜单栏
        openFile = QAction(QIcon('open.png'), '选择数据库(DB)', self)
        openFile.setShortcut('Ctrl+O')
        openFile.setStatusTip('选择数据库(DB)')
        openFile.triggered.connect(self.showDialog)

        # 工具栏
        exitAction = QAction(QIcon('run.png'), '执行', self)
        exitAction.setShortcut('Ctrl+Q')
        exitAction.triggered.connect(self.sqlrun)
        self.toolbar = self.addToolBar('执行')
        self.toolbar.addAction(exitAction)

        widget = QWidget();
        self.dbLabel = QLabel(self.DBPathTitle+"---------")
        self.msgLabel = QLabel("错误信息显示")
        self.textEdit = QTextEdit("")
        self.tableWidget = QTableWidget()
        vbox = QVBoxLayout()
        vbox.addWidget(self.dbLabel)
        vbox.addWidget(self.textEdit)
        vbox.addWidget(self.msgLabel)
        vbox.addWidget(self.tableWidget)
        self.setCentralWidget(widget)
        widget.setLayout(vbox)

        menubar = self.menuBar()
        fileMenu = menubar.addMenu('文件')
        fileMenu.addAction(openFile)

        self.resize(1000, 600)
        self.setWindowTitle('DBMangement')
        self.show()

    def showDialog(self):
        self.filePath, filetype = QFileDialog.getOpenFileName(self, '选取文件', "", "DB Files (*.db);;All Files (*)")
        print(self.filePath + " " + filetype)
        self.dbLabel.setText(self.DBPathTitle + self.filePath)

    def sqlrun(self):

        print("run")

        if not self.filePath:
            print("路径错误")
            self.msgLabel.setText("请先选择数据库")
            return

        fileName = str(self.filePath).split("/")
        fileName = fileName[len(fileName) - 1]
        fileName = fileName.split(".")
        fileName = fileName[0]
        print(fileName)


        # sql = "CREATE TABLE IF NOT EXISTS tester(timestamp DATETIME, uuid TEXT)"
        # cur = sqliteinit.execute(sql)
        # print(cur)
        # # cur = sqliteinit.execute("INSERT into tester values (?, ?)",("2010-01-01 13:00:00", "bow1"))
        # # print(cur)
        # sql = "SELECT *  from tester "

        try:
            self.tableWidget.clear()
            sqliteinit = SqliteInner()
            db = sqliteinit.setConnetionWithPath(fileName, self.filePath)
            sql = self.textEdit.toPlainText()
            print(sql)
            if sql:
                begin = datetime.datetime.now()
                cur = sqliteinit.execute(sql)
                end = datetime.datetime.now()
                print(type(cur))
                if cur is not None and len(cur) > 0 :
                    if type(cur) == list:
                        self.msgLabel.setText("执行时间：" + str(end-begin))
                        self.tableWidget.setRowCount(len(cur))
                        self.tableWidget.setColumnCount(len(cur[0]))
                        col_name_list = [tuple[0] for tuple in db._sqlite3_cursor.description]
                        print("name:" + str(col_name_list))
                        self.tableWidget.setHorizontalHeaderLabels(col_name_list)
                        # self.msgLabel.setText(str(cur))
                        begin = datetime.datetime.now()
                        for row in range(len(cur)):
                            columns = cur[row]
                            for l in range(len(columns)):
                                column = columns[l]
                                # print(column)
                                self.tableWidget.setItem(row, l, QTableWidgetItem(str(column)))
                        end = datetime.datetime.now()
                        print("渲染时间：" + + str(end-begin))
                    else:
                        self.msgLabel.setText(str(cur))
                else:
                    self.msgLabel.setText("查询结果为空")
            else:
                self.msgLabel.setText("请先输入sql语句")

        except Exception as e:
            print("执行数据库出错，错误语句:" + str(e))
            self.msgLabel.setText(str(e))

        self.tableWidget.show()
        sqliteinit.closedb()


if __name__ == '__main__':
    app = QApplication(sys.argv)
    ex = DataMangement()
    sys.exit(app.exec_())
